<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2018-09-11
 * Time: 15:40
 *
 * xlsx文档操作
 */

namespace app\index\controller;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Color;

class MyXlsx
{
    //生成周报表
    public static function outWeekEnergyXlsx()
    {
        MyXlsx::outXlsx('5月租户用电统计表.xls');
    }
    public static function deleteXlsx($path)
    {
        deleteFileIn($path);
    }
    //模板操作生成报表
    public static function outXlsx($templateName, $showname, $data1 = null, $data2 = null, $data3 = null, $data4 = null, $data5 = null)
    {
        //通过工厂模式创建内容
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('/www/wwwroot/template/'.$templateName);
        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->setCellValue('A1', $showname);

        $floors  = ['B6', 'B5', 'B4', 'B3', 'B2', 'B1', '1F', '2F', '3F', '4F', '5F', '6F', '7F', '8F', '9F'];

        $currentRow = 4;
        if(!empty($data1)) {
            $worksheet->setCellValue('C2', $data1['datetime']);
            $meterAllNum = $data1['meterNums'];

            for($x = 0; $x < count($floors); $x++)
            {
                for($y = 0; $y < count($meterAllNum); $y++)
                {
                    if($meterAllNum[$y]['floor'] == $floors[$x]) {
                        $spreadsheet->getActiveSheet()->getRowDimension($currentRow.'')->setRowHeight(23.5);  //设置行高
                        self::cellSetValue($spreadsheet, 'A'.$currentRow, $meterAllNum[$y]['floor']);
                        self::cellSetValue($spreadsheet, 'B'.$currentRow, $meterAllNum[$y]['shopName']);
                        self::cellSetValue($spreadsheet, 'C'.$currentRow, $meterAllNum[$y]['readNum']);
                        $currentRow++;
                    }
                }
            }
        }

        if(!empty($data2)) {
            $worksheet->setCellValue('E2', $data1['datetime']);
            $meterAllNum = $data2['meterNums'];

            for($currentRow1 = 4; $currentRow1 < $currentRow; $currentRow1++)
            {
                for($y = 0; $y < count($meterAllNum); $y++)
                {
                    if($meterAllNum[$y]['shopName'] == $worksheet->getCell('B'.$currentRow1)->getValue()) {
                        $spreadsheet->getActiveSheet()->getRowDimension($currentRow1.'')->setRowHeight(23.5);  //设置行高
                        self::cellSetValue($spreadsheet, 'E'.$currentRow1, $meterAllNum[$y]['readNum']);
                        $cha = $worksheet->getCell('E'.$currentRow1)->getValue() - $worksheet->getCell('C'.$currentRow1)->getValue();
                        self::cellSetValue($spreadsheet, 'D'.$currentRow1, $cha);
                    }
                }
            }
        }
        if(!empty($data3)) {
            $worksheet->setCellValue('G2', $data1['datetime']);
            $meterAllNum = $data3['meterNums'];

            for($currentRow1 = 4; $currentRow1 < $currentRow; $currentRow1++)
            {
                for($y = 0; $y < count($meterAllNum); $y++)
                {
                    if($meterAllNum[$y]['shopName'] == $worksheet->getCell('B'.$currentRow1)->getValue()) {
                        $spreadsheet->getActiveSheet()->getRowDimension($currentRow1.'')->setRowHeight(23.5);  //设置行高

                        self::cellSetValue($spreadsheet, 'G'.$currentRow1, $meterAllNum[$y]['readNum']);

                        $cha = $worksheet->getCell('G'.$currentRow1)->getValue() - $worksheet->getCell('E'.$currentRow1)->getValue();
                        self::cellSetValue($spreadsheet, 'F'.$currentRow1, $cha);
                    }
                }
            }
        }
        if(!empty($data4)) {
            $worksheet->setCellValue('I2', $data1['datetime']);
            $meterAllNum = $data4['meterNums'];

            for($currentRow1 = 4; $currentRow1 < $currentRow; $currentRow1++)
            {
                for($y = 0; $y < count($meterAllNum); $y++)
                {
                    if($meterAllNum[$y]['shopName'] == $worksheet->getCell('B'.$currentRow1)->getValue()) {
                        $spreadsheet->getActiveSheet()->getRowDimension($currentRow1.'')->setRowHeight(23.5);  //设置行高

                        self::cellSetValue($spreadsheet, 'I'.$currentRow1, $meterAllNum[$y]['readNum']);
                        $cha = $worksheet->getCell('I'.$currentRow1)->getValue() - $worksheet->getCell('G'.$currentRow1)->getValue();
                        self::cellSetValue($spreadsheet, 'H'.$currentRow1, $cha);
                    }
                }
            }
        }
        if(!empty($data5)) {
            $worksheet->setCellValue('K2', $data1['datetime']);
            $meterAllNum = $data5['meterNums'];

            for($currentRow1 = 4; $currentRow1 < $currentRow; $currentRow1++)
            {
                for($y = 0; $y < count($meterAllNum); $y++)
                {
                    if($meterAllNum[$y]['shopName'] == $worksheet->getCell('B'.$currentRow1)->getValue()) {
                        $spreadsheet->getActiveSheet()->getRowDimension($currentRow1.'')->setRowHeight(23.5);  //设置行高

                        self::cellSetValue($spreadsheet, 'K'.$currentRow1, $meterAllNum[$y]['readNum']);
                        $cha = $worksheet->getCell('K'.$currentRow1)->getValue() - $worksheet->getCell('I'.$currentRow1)->getValue();
                        self::cellSetValue($spreadsheet, 'J'.$currentRow1, $cha);
                    }
                }
            }
        }
        //合计
        self::cellSetValue($spreadsheet, 'B'.$currentRow, '合计');
        $spreadsheet->getActiveSheet()->getRowDimension($currentRow.'')->setRowHeight(23.5);  //设置行高

        $allData1 = 0;
        for($x = 4; $x < $currentRow; $x++)
        {
            $allData1 = $allData1 + $worksheet->getCell('D'.$x)->getValue();
        }
        self::cellSetValue($spreadsheet, 'D'.$currentRow, $allData1);
        //
        $allData2 = 0;
        if(!empty($data3)) {
            for($x = 4; $x < $currentRow; $x++)
            {
                $allData2 = $allData2 + $worksheet->getCell('F'.$x)->getValue();
            }
            self::cellSetValue($spreadsheet, 'F'.$currentRow, $allData2);
        } else {
            self::cellSetValue($spreadsheet, 'E'.$currentRow, $allData1 + $allData2);
        }
        $allData3 = 0;
        if(!empty($data4)) {

            for($x = 4; $x < $currentRow; $x++)
            {
                $allData3 = $allData3 + $worksheet->getCell('H'.$x)->getValue();
            }
            self::cellSetValue($spreadsheet, 'H'.$currentRow, $allData3);
        } else {
            self::cellSetValue($spreadsheet, 'G'.$currentRow, $allData1 + $allData2 + $allData3);
        }
        $allData4 = 0;
        if(!empty($data5)) {
            for($x = 4; $x < $currentRow; $x++)
            {
                $allData4 = $allData4 + $worksheet->getCell('J'.$x)->getValue();
            }
            self::cellSetValue($spreadsheet, 'J'.$currentRow, $allData4);
            self::cellSetValue($spreadsheet, 'K'.$currentRow, $allData1 + $allData2 + $allData3 + $allData4);  //保存总量
        } else {
            self::cellSetValue($spreadsheet, 'I'.$currentRow, $allData1 + $allData2 + $allData3);
        }

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls'); //通过工厂模式来写内容
        if(!file_exists('/www/wwwroot/upload/'.date('Ymd'))) {
            mkdir('/www/wwwroot/upload/'.date('Ymd'));
        }
        $path = '/www/wwwroot/upload/'.date('Ymd').'/'.$showname.'.xls';
        $writer->save($path);
        $path = str_replace("/www/wwwroot", "", $path);
        $path = "https://www.huikezk.com".$path;
        return $path;
    }
    private static function cellSetValue($spreadsheet,  $position, $value)
    {
        $styleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];

        $styleArray1 = [
            'borders' => [
                'outline' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_HAIR,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
        ];
        $worksheet = $spreadsheet->getActiveSheet();

        $worksheet->getStyle($position)->applyFromArray($styleArray);
        $worksheet->getStyle($position)->applyFromArray($styleArray1);
        $worksheet->setCellValue($position, $value);
    }
}
